<?php
/**
 * Created by PhpStorm.
 * User: lzq
 * Date: 2018-11-7
 * Time: 10:13
 */

namespace backend\controllers;

use yii;
use yii\web\Controller;
use backend\models\UploadForm;
use backend\models\Goods;
use backend\models\GoodsClass;
use backend\models\Logs;
use yii\web\UploadedFile;
use backend\models\SendEmail;
use backend\models\User;
use yii\helpers\Json;

class ExportController extends Controller
{
    //public $enableCsrfValidation = false;
    /**
     * {@inheritdoc}
     */
    public function behaviors()
    {
        return [

            'access'=>[
                'class'=> \backend\components\PermissionBehavior::className(),

            ],


        ];
    }
    //导入
    public function actionIn(){
//        var_dump(Yii::$app->request->isPost);
        $model = new UploadForm();
        if (Yii::$app->request->isPost) {
            $model->file = UploadedFile::getInstance($model, 'file');

            if ($model->upload()) {
                // 文件上传成功

                $fileType   = \PHPExcel_IOFactory::identify($model->filename); //文件名自动判断文件类型
                $excelReader  = \PHPExcel_IOFactory::createReader($fileType);


                $phpexcel    = $excelReader->load($model->filename)->getSheet(0);//载入文件并获取第一个sheet

                $total_line  = $phpexcel->getHighestRow();//总行数
//                $total_column= $phpexcel->getHighestColumn();//总列数
//                var_dump($total_line,$total_column);



                $insertnum = $updatenum=$failnum=0;

                $upd_info=$insert_info=$fail_info='';
                $yunyings = [];
//                $yunyings_email =$yunyings_name= [];

                for($row = 2; $row <= $total_line; $row++) {
                    $goodsno = $phpexcel->getCellByColumnAndRow( 0, $row )->getValue();
                    $goodsname =  $phpexcel->getCellByColumnAndRow( 1, $row )->getValue();
                    $class = $phpexcel->getCellByColumnAndRow( 2, $row )->getValue();
                    $goodsid = $phpexcel->getCellByColumnAndRow( 3, $row )->getValue();
                    $nextid = $phpexcel->getCellByColumnAndRow( 4, $row )->getValue();
                    $inprice = $phpexcel->getCellByColumnAndRow( 5, $row )->getValue();
                    $priceunit = $phpexcel->getCellByColumnAndRow( 6, $row )->getValue();
                    $beizhu = $phpexcel->getCellByColumnAndRow( 7, $row )->getValue();
//                    if(empty($beizhu)){
//                        $beizhu='';
//                    }
                    $beizhu=(string)$beizhu;
                    $yunying = User::findOne($nextid);

                    if(empty($yunying)){
                        $failnum++;
                        $fail_info.=$goodsname.' 对应的一级审核（运营）用户不存在<br>';
                        continue;
                    }
                    $fenlei = GoodsClass::findOne($class);
                    if(empty($fenlei)){
                        $failnum++;
                        $fail_info.=$goodsname.' 对应的商品分类不存在<br>';
                        continue;
                    }
                    if(($priceunit!=0 && $priceunit!=1) || (empty($priceunit) && $priceunit!=0)){
                        $failnum++;
                        $fail_info.=$goodsname.' 对应的币种不存在<br>';
                        continue;
                    }

                    //查重运营邮箱
                    if(!isset($yunyings[$yunying->username])){
//                        $yunyings_email[]=$yunying->email;
//                        $yunyings_name[]=$yunying->realname ? $yunying->realname :$yunying->username;
                        $yunyings[$yunying->username]['user']=$yunying;
                        $yunyings[$yunying->username]['totalnum']=0;
                        $yunyings[$yunying->username]['upnum']=0;
                        $yunyings[$yunying->username]['upinfo']=[];
                        $yunyings[$yunying->username]['downnum']=0;
                        $yunyings[$yunying->username]['downinfo']=[];
                        $yunyings[$yunying->username]['insertnum']=0;
                        $yunyings[$yunying->username]['insertinfo']=[];
                        $yunyings[$yunying->username]['updatenum']=0;
                        $yunyings[$yunying->username]['updateinfo']=[];

                    }




                    $createtime = date("Y-m-d H:i:s");
                    $price_remark='';
                    //查找是否以前有过
                    $goods = new Goods();
                    $good = $goods->find()->where(['goodsid'=>$goodsid])->one();
                    if(!empty($good)){//以前有过需要对比
//                        $oldlastprice=$good->lastprice;
                        if($good->gys!=Yii::$app->user->id){
                            $failnum++;
                            $fail_info.=$goodsname.' 与其他供应商SKUID冲突，跳过<br>';
                            continue;
                        }
                        $oldprice = $good->lastprice;
                        $oldupfee=$good->upfee;
                        $newprice =$inprice;
                        $remark='';
                        if(empty($newprice)){
                            $failnum++;
                            $fail_info.=$goodsname.' 新价格不正确，跳过<br>';
                            continue;
                        }
                        if($good->check!=3 && $good->check!=4){
                            $failnum++;
                            $fail_info.=$goodsname.' 审核中不允许修改，跳过<br>';
                            continue;
                        }
                        $oprtype = "批量导入_更新商品";
                        $model = Goods::findOne($good->id);//修改记录而不是添加
                        $isupd = false;

                        if($oldprice != $newprice){


                            $unit = $model->priceunit ? "$":"￥";
                            $newunit=$priceunit ? "$" :"￥";
                            $model->inprice = $newprice;
                            $model->upfee = Yii::$app->formatter->asDecimal(($newprice-$oldprice)/$oldprice, 4) * 100 ;
                            if($oldprice > $newprice){//旧价格大于等于现在价格（降价或原价）直接免审核
                                $check=3;
                                $remark .='更改审核状态为三审通过 ';
//                                $progress=Json::decode($model->progress);
                                $new_progress=[['status'=>'审核通过', 'opr'=>'系统','oprtime'=>date('Y-m-d H:i:s')]];
                                $model->progress=Json::encode($new_progress);
                                $model->lastprice = $newprice;


                                $price_remark='<span style="color:green">'.$unit.$oldprice.'↘'.$newunit.$newprice.'</span><br>';
                                $price_remark.='<span style="color:green">'.$oldupfee.'%↘'.$model->upfee.'%</span>';
                                $yunyings[$yunying->username]['downnum']++;
                                $yunyings[$yunying->username]['downinfo'][]=$goodsname;
                            }else{//需要审核,先保存上次价格
                                $check=0;
                                $remark .='更改审核状态为待审核 ';
                                $price_remark='<span style="color:red">'.$unit.$oldprice.'↗'.$newunit.$newprice.'</span><br>';
                                $price_remark.='<span style="color:red">'.$oldupfee.'%↗'.$model->upfee.'%</span>';
                                $new_progress=[['status'=>'待审核', 'opr'=>Yii::$app->user->identity->realname,'oprtime'=>date('Y-m-d H:i:s')]];
                                $model->progress=Json::encode($new_progress);
                                $yunyings[$yunying->username]['upnum']++;
                                $yunyings[$yunying->username]['upinfo'][]=$goodsname;
                            }
                            $model->check=$check;



                            $remark .='更改价格（'.$unit.$oldprice.' to '.$newunit.$newprice.'） ';
                            $remark .='更改上次价格（'.$unit.$oldprice.' to '.$newunit.$newprice.'） ';
                            $remark .='更改涨幅（'.$oldupfee.' to '.$model->upfee.'） ';
                            $isupd=true;
                           // echo "inprice";
                        }
                        if($good->goodsname!=$goodsname){

                            $isupd=true;
                            $remark .='更改商品名称（'.$good->goodsname.' to '.$goodsname.'） ';
                            $model->goodsname=$goodsname;
                          //  echo "goodsname";

                        }
                        if($good->class!=$class){

                            $isupd=true;
                            $remark .='更改商品分类（'.$good->class.' to '.$class.'） ';
                            $model->class=$class;
                           // echo "class";
                        }
                        if($good->nextid!=$nextid){

                            $isupd=true;
                            $remark .='更改运营商（'.$good->nextid.' to '.$nextid.'） ';
                            $model->nextid=$nextid;
                          //  echo "nextid";
                        }
                        if($good->priceunit!=$priceunit){

                            $isupd=true;
                            $remark .='更改币种（'.$good->priceunit.' to '.$priceunit.'） ';
                            $model->priceunit=$priceunit;
                           // echo "priceunit";
                        }
                        if($good->remark!=$beizhu){

                            $isupd=true;
                            $remark .='更改备注（'.$good->remark.' to '.$beizhu.'） ';
                            $model->remark=$beizhu;
                            // echo "priceunit";
                        }
                        if($isupd){
                            $model->updatetime=$createtime;
                            $model->export=0;
                            $remark .='更改为未导出状态 ';

                            $model->save();
                            $upd_info .= $goodsname." 更新成功<br>";
                            $updatenum++;
                            $yunyings[$yunying->username]['updatenum']++;
                            $yunyings[$yunying->username]['updateinfo'][]=$goodsname;
                            $yunyings[$yunying->username]['totalnum']++;

                            $logs = new Logs();
                            $logs->goodsid = $model->id;
                            $logs->opid = Yii::$app->user->id;
                            $logs->oprtype = $oprtype;
                            $logs->remark = $remark;
                            $logs->price_remark=$price_remark;
                            $logs->oprtime = date('Y-m-d H:i:s');

                            $logs->save();

                        }else{
                            $failnum++;
                            $fail_info.=$goodsname.' 无更新，跳过<br>';
                        }

                        continue;
                    }else{//新商品
                        if(empty($inprice)){
                            $failnum++;
                            $fail_info.=$goodsname.' 新价格不正确，跳过<br>';
                            continue;
                        }
                        $insert_info.=$goodsname.' 创建成功<br>';
                        $model = new Goods();
                        $model->goodsno = (string)$goodsno;
                        $model->goodsname =  $goodsname;
                        $model->class = $class;
                        $model->gys = Yii::$app->user->id;
                        $model->goodsid = $goodsid;
                        $model->nextid = $nextid;
                        $model->inprice = $inprice;
                        $model->lastprice = $inprice;
                        $model->upfee = 0;
                        $model->priceunit = $priceunit;
                        $model->check = 3;
                        $model->export = false;
                        $model->remark=$beizhu;
                        $model->createtime=date("Y-m-d H:i:s");
                        $model->updatetime=date("Y-m-d H:i:s");
                        $model->progress=Json::encode([

                            ['status'=>'审核通过', 'opr'=>'系统','oprtime'=>date('Y-m-d H:i:s')]

                        ]);
                        if(!$model->save()){
                          var_dump($model->getErrors());
                          exit;
                        }

//                        var_dump($model->id);exit;
                        $logs = new Logs();
                        $logs->goodsid = $model->id;
                        $logs->opid = Yii::$app->user->id;
                        $logs->oprtype = '批量导入_创建商品';
                        $logs->remark = $goodsname;
                        $logs->oprtime = date('Y-m-d H:i:s');

                        $logs->save();
                    }


//                    $excelData[$row-1]=compact('goodsno','goodsname','class','goodsid','nextid','gys','inprice','priceunit','check','createtime','export');
                    $insertnum++;
                    $yunyings[$yunying->username]['insertnum']++;
                    $yunyings[$yunying->username]['insertinfo'][]=$goodsname;
                    $yunyings[$yunying->username]['totalnum']++;
                }
                $total_line=$total_line-1;

                $send_info='';
                if(!empty($yunyings)  ){
                    $sendmail=new SendEmail();
                    foreach($yunyings as $item){
                        $sendmail->name='供货价管理系统';
                        $sendmail->email=Yii::$app->params['adminEmail'];
                        $sendmail->subject=Yii::$app->user->identity->realname.'创建'.$item['totalnum'].'个新商品';
                        $sendinfo = '供应商：'.Yii::$app->user->identity->realname.'<br>累计导入'.$item['totalnum'].'个新商品,其中'.$item['upnum'].'个商品上涨，'.$item['downnum'].'个商品下跌';
//                        插入成功{$item['insertnum']}条！修改成功{$item['updatenum']}条！";
                        $sendinfo.= "<br>";
                        if(!empty($item['insertinfo'])) {
                            $sendinfo.= "插入信息：";
                            $sendinfo.= "<br>";
                            foreach($item['insertinfo'] as $bookname){
                                $sendinfo.= $bookname;
                                $sendinfo.= "<br>";
                            }

                        }
                        if(!empty($item['updateinfo'])) {
                            $sendinfo.= "更新信息：";
                            $sendinfo.= "<br>";
                            foreach($item['updateinfo'] as $bookname){
                                $sendinfo.= $bookname;
                                $sendinfo.= "<br>";
                            }

                        }
                        if(!empty($item['upinfo'])) {
                            $sendinfo.= "上涨商品：";
                            $sendinfo.= "<br>";
                            foreach($item['upinfo'] as $bookname){
                                $sendinfo.= $bookname;
                                $sendinfo.= "<br>";
                            }

                        }
//                        if(!empty($item['downinfo'])) {
//                            $sendinfo.= "下跌商品：";
//                            $sendinfo.= "<br>";
//                            foreach($item['downinfo'] as $bookname){
//                                $sendinfo.= $bookname;
//                                $sendinfo.= "<br>";
//                            }
//
//                        }
//                        if(!empty($fail_info)) {
//                            $sendinfo.= "错误信息：";
//                            $sendinfo.= "<br>";
//                            $sendinfo.= $fail_info;
//                        }
//                        if(!empty($send_info)){
//                            $sendinfo.= "邮件发送结果";
//                            $sendinfo.= '<br>';
//                            $sendinfo.= $send_info;
//                        }
                        $sendmail->body='供货商：《'.Yii::$app->user->identity->realname .'》<br>批量导入结果如下：<br>'.$sendinfo;
                        if ($sendmail->sendEmail($item['user']->email)) {
                             $send_info .= '给运营《'.$item['user']->realname."》的邮件已发送成功！";
                            $send_info.= "<br>";
                        } else {
                            $send_info .= '给运营《'.$item['user']->realname."》的邮件已发送失败，请其他方式通知对方！";
                            $send_info.= "<br>";
                        }
                    }

                }



                return $this->render('in',[
                    'total_line'=>$total_line,
                    'insertnum'=>$insertnum,
                    'updatenum'=>$updatenum,
                    'failnum'=>$failnum,
                    'upd_info'=>$upd_info,
                    'insert_info'=>$insert_info,
                    'fail_info'=>$fail_info,
                    'send_info'=>$send_info,
                ]);



            }
        }

        //return $this->redirect('/goods/index',['model'=>$model]);

    }
    /**
     * @inheritdoc  批量添加
     * @params   $add  array 添加数据
     */
    public function add_all($add)
    {
        $connection = \Yii::$app->db;




        //数据批量入库
        return $connection->createCommand()->batchInsert(
            'goods',
            ['goodsno','goodsname','class','goodsid','nextid','gys','inprice','priceunit','check','createtime','export'],//字段
            $add
        )->execute();
    }

    //导出
    public function actionOut(){
        $query = Goods::find();
        $param = Yii::$app->request->get('GoodsSearch');
//        print_r($param);exit;
        $emptyarray=['goodsid' =>'' ,'goodsno' => '','gys' => 0 ,'upfee' => 0,'class' => 0, 'datefrom' =>'', 'dateto' =>'', 'export' => 2 ];
        $flag=true;
        foreach($emptyarray as $key=>$val){
            if($param[$key]!=$emptyarray[$key]){
              $flag=false;
            }
        }

        if($flag==false){
            if($param['goodsid']){
                $query->andFilterWhere(['like', 'goodsid', $param['goodsid']]);
            }
            if($param['goodsno']){
                $query->andFilterWhere(['like', 'goodsno', $param['goodsno']]);
            }
//            if($param['goodsname']){
//                $query->andfilterWhere(['like','goodsname',$param['goodsname']]);
//            }
            if($param['gys']){
                $query->andFilterWhere(['gys'=>$param['gys']]);
            }
            if($param['upfee']==1){
                $query->andWhere(['>','upfee','0']);
            }elseif($param['upfee']==2){
                $query->andFilterWhere(['<','upfee','0']);
            }



            if($param['class']){
                $query->andFilterWhere(['class'=>$param['class']]);
            }
            if($param['datefrom']){
                $query->andFilterWhere(['<','createtime',$param['datefrom']]);
            }
            if($param['dateto']){
                $query->andFilterWhere(['>','createtime',$param['dateto']]);
            }
            if($param['export']!=2){
                $query->andFilterWhere(['export'=>$param['export']]);
            }
            $total_count=$query->count();

        }else{
//            $query->andFilterWhere(['check'=>3]);
//            ->andFilterWhere(['export'=>0]);

            $total_count= Goods::find()->count();
//            var_dump($total_count);exit;
        }
        $query->andFilterWhere(['check'=>3]);
//         $commandQuery = clone $query;
//        echo $commandQuery->createCommand()->getRawSql();
//exit;

        $export_count=$query->count();
        $data=$query->all();

        if(empty($data)){


            Yii::$app->session->setFlash('error', '审核中和审核不通过的数据不允许导出！');
            return $this->redirect('/goods/index');
        }
        if(!empty($param)) {

            $result = $this->export($data,false);
        }else{
            $result = $this->export($data);
        }
        Yii::$app->session->setFlash('success', '总计'.$total_count.'条，导出'.$export_count.'条，未导出'.($total_count-$export_count).'条！');
       return $this->render('out',['filename'=>$result]);
    }

    public function export($data,$default=true){


        if($default){
            $filename = 'GoodsList_'.date("Y-m-d").'.xls';


        }else{
            $filename = 'GoodsList_'.date("YmdHis").'.xls';


        }
        $filename1 = Yii::$app->basePath.DIRECTORY_SEPARATOR . "web".DIRECTORY_SEPARATOR."download" .DIRECTORY_SEPARATOR  . $filename;
        if(file_exists($filename1)){
            unlink($filename1);
        }

        $objPHPExcel = new \PHPExcel();
        $goodslist_head = array('货品编号','供应商','进货价','品名');

        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getProperties()->setCreator("联络供货价管理系统")
            ->setLastModifiedBy(Yii::$app->user->id)
            ->setTitle("商品列表")
            ->setSubject("商品列表")
            ->setDescription("商品列表")
            ->setKeywords("商品列表")
            ->setCategory("商品列表");
        $pe = $objPHPExcel->setActiveSheetIndex(0);//Excel的第A列，uid是你查出数组的键值，下面以此类推

        foreach($goodslist_head as $key=>$val){
            if($key<4){
                $pe  ->setCellValue(chr($key+65).'1', $val);
            }

        }

        /*以下就是对处理Excel里的数据， 横着取数据*/
        foreach($data as $k => $v){
            $gysname = User::findOne($v['gys'])->realname;
            $num=$k+2;
            $pe
                ->setCellValue('A'.$num, "'".$v['goodsno'])
                ->setCellValue('B'.$num, $gysname)
                ->setCellValue('C'.$num, $v['inprice'])
                ->setCellValue('D'.$num, $v['goodsname']);


            $good = Goods::findOne($v['id']);



                $good->export=1;






            $logs = new Logs();
            $logs->goodsid = $v['id'];
            $logs->opid = Yii::$app->user->id;
            $logs->oprtype = '批量导出';
            $logs->remark = "导出文件：<a href='/download/$filename'>{$v['goodsname']} </a> ";
            $logs->oprtime = date('Y-m-d H:i:s');

            $logs->save();
            if($good->progress){
                //新增进度
                $progress=Json::decode($good->progress,true);

                if(!$this->array_multi_search('导出',$progress)){
                    $new_progress=[['status'=>'导出', 'opr'=>Yii::$app->user->identity->realname,'oprtime'=>date('Y-m-d H:i:s')]];
                    $good->progress=Json::encode(array_merge($progress,$new_progress));
                }


            }

            $good->save();
        }

        $objPHPExcel->getActiveSheet()->setTitle('商品列表');
        $objPHPExcel->setActiveSheetIndex(0);
//        header('Content-Type: applicationnd.ms-excel');
//        header('Content-Disposition: attachment;filename="GoodsList_'.date("Y-m-d").'.xls"');
//        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save($filename1);

        return '/download/'.$filename;
//        echo "导出结果：";
//        echo "<a href='/download/GoodsList_".date("Y-m-d").".xls'>GoodsList_".date("Y-m-d").".xls</a><br>";
//        echo "<a href='/download/GoodsProvider_".date("Y-m-d").".xls'>GoodsProvider_".date("Y-m-d").".xls</a><br>";
//        exit;

    }
    function array_multi_search( $p_needle, $p_haystack )
    {

        if( in_array( $p_needle, $p_haystack ) )
        {
            return true;
        }
        foreach( $p_haystack as $key=>$row )
        {
            if(is_array($row)){
                if( $this->array_multi_search( $p_needle, $row ) )
                {
                    return true;
                }
            }else{
                if(  $row == $p_needle)
                {
                    return true;
                }
            }

        }

        return false;
    }
}